from fastapi import FastAPI, UploadFile, File, Form, Request, HTTPException
from fastapi.responses import HTMLResponse, RedirectResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
import os
import hashlib
import shutil
from pandas_excel import *
import pandas as pd
from html_templates import *
from urllib.parse import urlencode
from openai import OpenAI

###############################
import pandas as pd
import requests
import numpy as np
import webbrowser  # 新增：用于打开浏览器
import os  # 新增：用于文件路径处理
from typing import Optional
import ssl


app = FastAPI()

# 确保static文件夹存在
os.makedirs("static", exist_ok=True)

# 挂载静态文件目录
app.mount("/static", StaticFiles(directory="static"), name="static")

# 设置模板目录
templates = Jinja2Templates(directory="templates")


# 示例函数abc - 当文件已存在且为同一文件时执行
def read_json(filename: str):
    #df = pd.read_json('./static/'+filename+'.json')
    df = pd.read_csv('./static/'+filename+'.json')
    #print(df,type(df),df.shape)
    return [df]
    # 这里可以添加实际业务逻辑

# 示例函数bcd - 当文件不存在时执行
def save2json(filename: str):
    print(f"执行函数bcd: 新文件 {filename} 已上传，")
    file_path='./static/'+filename
    df=read_excel_with_auto_header(file_path)
    # 写入 JSON 文件
    #df.to_json('./static/'+filename+'.json')
    df.to_csv('./static/'+filename+'.json')
    return df
    #columns_names=df.columns.tolist()
    # 这里可以添加实际业务逻辑

# 计算文件的MD5哈希值，用于判断是否为同一文件
def get_file_hash(file_path: str) -> str:
    hash_md5 = hashlib.md5()
    with open(file_path, "rb") as f:
        for chunk in iter(lambda: f.read(4096), b""):
            hash_md5.update(chunk)
    return hash_md5.hexdigest()




# 首页 - 文件上传表单
@app.get("/", response_class=HTMLResponse)
async def upload_page(request: Request):
    # 直接返回HTML代码
    html_content='''
<!DOCTYPE html>
<html>
<head>
    <title>EXCEL表格AI分析助手</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <style type="text/tailwindcss">
        @layer utilities {
            .content-auto {
                content-visibility: auto;
            }
            .form-focus {
                @apply focus:ring-2 focus:ring-blue-500 focus:border-transparent focus:outline-none;
            }
            .transition-custom {
                @apply transition-all duration-300 ease-in-out;
            }
        }
    </style>
    <script>
        tailwind.config = {
            theme: {
                extend: {
                    colors: {
                        primary: '#165DFF',
                        secondary: '#36BFFA',
                        neutral: '#F5F7FA',
                        dark: '#1D2129',
                    },
                    fontFamily: {
                        inter: ['Inter', 'system-ui', 'sans-serif'],
                    },
                }
            }
        }
    </script>
</head>
<body class="font-inter bg-gray-50 min-h-screen flex flex-col">
    <!-- 顶部导航 -->
    <header class="bg-white shadow-sm py-4">
        <div class="container mx-auto px-4 sm:px-6 lg:px-8">
            <div class="flex justify-between items-center">
                <h1 class="text-[clamp(1.5rem,3vw,2rem)] font-bold text-primary">
                    EXCEL表格AI分析助手
                </h1>
                <div class="text-gray-500 text-sm">
                    支持xlsx上传
                </div>
            </div>
        </div>
    </header>

    <!-- 主要内容区 -->
    <main class="flex-grow container mx-auto px-4 sm:px-6 lg:px-8 py-8">
        <div class="max-w-2xl mx-auto">
            <!-- 卡片容器 -->
            <div class="bg-white rounded-xl shadow-md overflow-hidden transition-custom hover:shadow-lg">
                <!-- 卡片头部 -->
                <div class="bg-primary text-white p-6">
                    <h2 class="text-xl font-semibold">上传文件并提问</h2>
                    <p class="text-blue-100 text-sm mt-1">请选择需要上传的文件并输入您的问题</p>
                </div>
                
                <!-- 表单内容 -->
                <div class="p-6">
                    <form action="/upload" method="post" enctype="multipart/form-data" class="space-y-6">
                        <!-- 文件上传区域 -->
                        <div class="form-group">
                            <label for="file" class="block text-sm font-medium text-gray-700 mb-2">
                                选择文件
                            </label>
                            <div class="relative border-2 border-dashed border-gray-300 rounded-lg p-8 text-center transition-custom hover:border-primary/50 hover:bg-blue-50">
                                <input type="file" id="file" name="file" required 
                                    class="absolute inset-0 w-full h-full opacity-0 cursor-pointer"
                                    onchange="updateFileName(this)">
                                <div class="flex flex-col items-center">
                                    <p class="text-gray-500 mb-1" id="file-name">拖放文件到此处或点击选择文件</p>
                                    <p class="text-xs text-gray-400">支持 PDF, DOC, DOCX, XLS, XLSX 等格式</p>
                                </div>
                            </div>
                        </div>

                        <!-- 问题输入区域 -->
                        <div class="form-group">
                            <label for="question" class="block text-sm font-medium text-gray-700 mb-2">
                                问题描述，文件第一次上传稍慢，请耐心等待
                            </label>
                            <div class="relative">
                                <input type="text" id="question" name="question" required
                                    class="w-full pl-3 pr-3 py-3 border border-gray-300 rounded-lg form-focus transition-custom"
                                    placeholder="请输入您的问题...">
                            </div>
                        </div>

                        <!-- 提交按钮 -->
                        <div class="pt-2">
                            <button type="submit" 
                                class="w-full bg-primary hover:bg-primary/90 text-white font-medium py-3 px-4 rounded-lg transition-custom transform hover:scale-[1.02] active:scale-[0.98] flex items-center justify-center">
                                上传并提交
                            </button>
                        </div>
                    </form>
                </div>
            </div>

            <!-- 帮助信息 -->
            <div class="mt-6 bg-blue-50 border border-blue-100 rounded-lg p-4 text-sm text-blue-700">
                <div class="flex items-start">
                    <p>提示：请确保文件大小不超过10MB，问题描述清晰明确将有助于获得更准确的回应。</p>
                </div>
            </div>
        </div>
    </main>

    <!-- 页脚 -->
    <footer class="bg-white border-t border-gray-200 py-6">
        <div class="container mx-auto px-4 sm:px-6 lg:px-8 text-center text-gray-500 text-sm">
            <p>© 2023 文件上传系统 - 保留所有权利</p>
        </div>
    </footer>

    <script>
        // 更新显示的文件名
        function updateFileName(input) {
            const fileNameElement = document.getElementById('file-name');
            if (input.files && input.files.length > 0) {
                fileNameElement.textContent = input.files[0].name;
                fileNameElement.classList.add('text-primary', 'font-medium');
            } else {
                fileNameElement.textContent = '拖放文件到此处或点击选择文件';
                fileNameElement.classList.remove('text-primary', 'font-medium');
            }
        }

        // 添加表单提交动画效果
        document.querySelector('form').addEventListener('submit', function(e) {
            const submitButton = this.querySelector('button[type="submit"]');
            submitButton.disabled = true;
            submitButton.innerHTML = '处理中...';
        });
    </script>
</body>
</html>

'''
    return HTMLResponse(content=html_content, status_code=200)


# 处理文件上传
@app.post("/upload", response_class=HTMLResponse)
async def handle_upload(
    request: Request,
    file: UploadFile = File(...),
    question: str = Form(...)
):
    # 检查文件类型是否为xlsx
    if not file.filename.endswith(".xlsx"):
        return templates.TemplateResponse(
            "upload.html", 
            {
                "request": request,
                "message": "请上传XLSX格式的文件",
                "success": False
            }
        )
    
    # 构建文件保存路径
    file_path = os.path.join("static", file.filename)
    file_exists = os.path.exists(file_path)
    
    #try:
    # 保存上传的文件到临时位置
    temp_path = f"{file_path}.temp"
    print('temp_path',temp_path)
    with open(temp_path, "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)
    print(1)
    if file_exists:
        # 比较文件哈希值判断是否为同一文件
        existing_hash = get_file_hash(file_path)
        new_hash = get_file_hash(temp_path)
        print(2)
        
        if existing_hash == new_hash:
            print(3)
            # 同一文件，执行abc函数
            df=read_json(file.filename)
            os.remove(temp_path)  # 删除临时文件
            #return RedirectResponse(url="/query/?filename=xx.xlsx&q=2025年8月上海蔚来订单多少")
            # 准备要传递的参数
            params = {
                "filename": file.filename,
                "q": question
            }
            
            # 构建跳转URL，包含查询参数
            # 对于POST跳转，通常会使用查询参数传递数据
            redirect_url = f"/query/?{urlencode(params)}"
            
            # 跳转到第二个POST接口
            return RedirectResponse(url=redirect_url, status_code=307)
            return templates.TemplateResponse(
                "upload.html", 
                {
                    "request": request,
                    "message": f"文件 {file.filename} 已存在且为同一文件，已执行函数abc",
                    "success": True
                }
            )
        else:
            print(4)
            # 文件名相同但内容不同，覆盖文件
            os.remove(file_path)
            os.rename(temp_path, file_path)
            df=save2json(file.filename)
            df=read_json(file.filename)
            #os.remove(temp_path)  # 删除临时文件
            #return RedirectResponse(url="/query/?filename=xx.xlsx&q=2025年8月上海蔚来订单多少")
            # 准备要传递的参数
            params = {
                "filename": file.filename,
                "q": question
            }
            
            # 构建跳转URL，包含查询参数
            # 对于POST跳转，通常会使用查询参数传递数据
            redirect_url = f"/query/?{urlencode(params)}"
            return RedirectResponse(url=redirect_url, status_code=307)
            return templates.TemplateResponse(
                "upload.html", 
                {
                    "request": request,
                    "message": f"文件 {file.filename} 已存在但内容不同，已更新并执行函数bcd",
                    "success": True
                }
            )
    else:
        print(5)
        # 文件不存在，直接保存并执行bcd函数
        os.rename(temp_path, file_path)
        df=save2json(file.filename)
        df=read_json(file.filename)
        #os.remove(temp_path)  # 删除临时文件
            #return RedirectResponse(url="/query/?filename=xx.xlsx&q=2025年8月上海蔚来订单多少")
            # 准备要传递的参数
        params = {
                "filename": file.filename,
                "q": question
        }
            
            # 构建跳转URL，包含查询参数
            # 对于POST跳转，通常会使用查询参数传递数据
        redirect_url = f"/query/?{urlencode(params)}"
        return RedirectResponse(url=redirect_url, status_code=307)
        return templates.TemplateResponse(
            "upload.html", 
            {
                "request": request,
                "message": f"文件 {file.filename} 已上传，已执行函数bcd",
                "success": True
            }
        )
            
##    except Exception as e:
##        print(6)
##        return templates.TemplateResponse(
##            "upload.html", 
##            {
##                "request": request,
##                "message": f"上传失败: {str(e)}",
##                "success": False
##            }
##        )


@app.post("/second")
async def second_post(filename: str, q: str):
    # 处理接收到的参数
    return {
        "message": "成功从第一个POST接口跳转过来",
        "received_filename": filename,
        "received_q": q
    }

@app.post("/query/")
async def query_file(filename: str, q: str):
    print("WWWWWWWWWWWWWWWWWWWWWWW",filename,q)
    #http://127.0.0.1:8000/query/?filename=xx.xlsx&q=2025年8月上海蔚来订单多少
    """
    处理文件查询的接口
    
    参数:
    - filename: 要查询的文件名
    - q: 关于该文件的问题
    """
##    df=read_json(filename)
##    print("df ###########")
##    print(df)
##    #df=read_excel_with_auto_header(file_path)
##    columns_names=df.columns.tolist()
##    user_question=q
##    print('**************用户的问题是？',user_question)
    dfs=read_json(filename)
    #ai_generated_content=get_ai_python_code(dfs,q)
    ai_generated_content=get_ai_python_code(dfs,q,base_url='http://bjg4gpuskv1p.accounts.intern/v1/chat/completions',api_key='gpustack_d21a1f378b17041d_b5c6ef870b54d6ec932183d83c82c08c',model='prd-qwen3-235b-a22b-instruct-2507-Q2_K_XL')
    print('ai_generated_content',ai_generated_content)
    result0=run_ai_generated_code(dfs,ai_generated_content)
    html0=generate_tables1(q,dataframe_to_html_table(result0['value']),filename)

    return HTMLResponse(content=html0, status_code=200)

     # 用系统默认浏览器打开
    #write2html(html_content,user_question)
##    return {
##        "filename": filename,
##        "question": q,
##        "message": f"您查询的文件是: {filename}, 问题是: {q}"
##    }

if __name__ == "__main__":
    requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
    import uvicorn
    #uvicorn.run("main.py", host="0.0.0.0", port=8000, reload=True)
    webbrowser.open_new("http://localhost:8000")
    uvicorn.run(app, host="0.0.0.0", port=8000)
